Where there is more than one .csv file

Note: If you haven't commented out MainDocument.Close SaveChanges:=False in the VB code, you will want to do so now for testing purposes. Details can be found in 1. Write the VB code (Step 9).

In our earlier example we created the following mail merge report, drawing data from a single .csv file called eloans.csv, which includes Parties and Loans information:

In 1. Write the VB code we established a connection between our mail merge report and the main data source, eloans.csv. All of the data displaying in the above report is drawn from this .csv file, and includes details about the loan and the borrower.

However we also want to include details about the objects on loan, but this data is not in eloans.csv ; it is drawn from a field called Object: (Object Loaned) - which is an attachment field and a table of values. As we have seen, values in a field that is both an attachment field AND a table of values will always be exported in a separate .csv file. In order to include data from this second (third, nth) .csv file we need to add more Visual Basic code to our mail merge report.

To edit the main report document in Word:

1. Open the main report document (LoanAgreement.doc in this example) and enable macros when asked.
2. If necessary, select ALT+F9 to display field codes:

   

3. We're going to include the object details in a table. Place the cursor where the table is to display:

   

4. For this example, we'll insert a two cell, single row table:

   

5. With the cursor in the first cell, click Insert Merge Fields in the Mail Merge Tool bar.

   Note: If the Mail Merge Tool bar does not display, select Tools>Letters and Mailings>Show Mail Merge Tool bar from the MS Word Menu bar.

   The Insert Merge Field box displays.

6. Select the key field that links the main .csv file (eloans.csv in this example) and the attached table from which we wish to draw data (ObjObjec.csv in this example). In this example, we select eloans_key:

   

   See Step 1: Create the report in EMu for details about which .csv files are generated in this example.

7. Click Insert and then close the Insert Merge Field box:

   

8. Make sure that the font for this table matches the font of the other text in your document.

Next we need to add Visual Basic code.

1. Select Tools>Macro>Visual Basic Editor (ALT+F11) from the Menu bar.

   The Microsoft Visual Basic editor opens:

   

2. In this example we enter the following code immediately after the code that connects our mail merge report to the main data source.

   An explanation of the code is included as comments:

   Dim key As String

   Dim table As Word.table

   Dim objects As String

   Dim connection As New ADODB.connection

   Dim recordset As New ADODB.recordset

   '

   ' Connect to the DSN that we are going to use

   connection.Open "DSN=EMu Loans;"

   '

   ' This code cycles through each table we have added to the Word document in order to populate

   ' it with data. In this example we have only one table.

   For Each table In ActiveDocument.Tables

       ' Our table has two columns

       If table.Columns.Count = 2 Then

           '

           ' In the first cell of a table in our mail merge document we have inserted

           ' the key field that links the various .csv files (in our example, eloans_key).

           ' We need to get the value of this key in order to query the second (third, nth) .csv file.

           ' This code takes the value of the key from the first cell in the table

           key = table.Cell(1, 1).Range.Text

           key = Trim(Left(key, Len(key) - 2))

    

           objects = ""

           '

           ' This code queries the second .csv file

           recordset.Open "select * from ObjObjec.csv where eloans_key = " & key, connection

           '

           ' This code populates the table in our mail merge report with data

           ' (from the Summary Data field in our second .csv file in this example)

           ' one row for each record

           While Not recordset.EOF

               If objects <> "" Then

                   bjects = bjects & Chr(10)

               End If

               bjects = bjectss & recordset("SummaryData")

               recordset.MoveNext

           Wend

           table.Cell(1, 2).Range.Text = objects

           recordset.Close

           '

           ' This code then removes the column containing the eloans_key from our mail merge table

           ' so that it doesn't display in the mail merge report

           table.Columns(1).Select

           Selection.Columns.Delete

           '

           'This removes borders from the table

           table.Borders.Enable = False

       End If

   Next table

   '

   ' Close down our connections

     connection.Close

   Note: In this example we're populating the table with one value (Summary Data) for each object in the linked .csv file (a loan may involve one or many objects). This code will generate a static table: a table with a single row within which all the linked records are listed, one line for each object record (see Step 4: Run the mail merge for an example of how this mail merge report will display).
   If we wanted to display two values for each object in the linked .csv file (e.g. IRN and Summary Data), a static table might be inadequate as we'd require two columns to hold the two values, but because we have a single row, the values in the two columns might get out of sync. What we require in this case is a row for each linked record: this requires code to generate a dynamic table.

3. Save and close the main report document.
4. At 1. Write the VB code we modified the VB code for testing purposes by commenting out MainDocument.Close SaveChanges:=False. Before we finalise the report we need to edit the VB code to remove the apostrophe.